<?php

namespace app;

class Mysql extends Base
{
    protected $arrCharacter = [
        '>'      => '$gt',
        '>='     => '$gte',
        '<'      => '$lt',
        '<='     => '$lte',
        '!='     => '$ne',
        '<>'     => '$ne',
        'in'     => '$in',
        'not in' => '$nin',
    ];

    public function test()
    {
        $sql = "select uid, count(uid) count from t_login " .
            "where opmode = 2 and (gcid1 in ('123456', '12345611') or gcid2 in ('123456', '12345611')) " .
            "group by uid having count < 2 order by count";
        $sql = 'select 
* 
from t_login where nickname like "lss" AND name is null and      id = 1    and age = 18  and id in (1,2,3,4)
order by id desc, name asc group by player_id having count > 0 and sum(*) =0';
        $sql = strtolower($sql);
//        $sql = 'update t_login set name="lss"';
//        $sql = 'delete * from t_login where name="hehe"';
        $preg = '/^(select|update|delete)\s+/';
        preg_match($preg, $sql, $match);
        switch ($match[1]) {
            case 'select':
                return $this->forSelect($sql);
                break;
            case 'update':

                break;
            case 'delete':

                break;
            default:
                throw new \Exception('错误');
        }
    }

    public function forSelect($sql)
    {
        echo "<pre>";

        // 取出多余空格
        $sql = preg_replace('/\s(?=\s)/', '', $sql);
        // 换行符、转义符
        $sql  = preg_replace('/\n|\r|\t/', ' ', $sql);
        $preg = '/select(.*?)from(.*?)where(.*?)$/';
        preg_match($preg, $sql, $arrMatch);

        $newSql     = $arrMatch[0];
        $field      = $arrMatch[1];
        $table      = $arrMatch[2];
        $afterWhere = $arrMatch[3];
        if (strpos($afterWhere, 'order by') !== false) {
            $preg = '/(.*?)order\sby(.*?)$/';
            preg_match($preg, $afterWhere, $arrMatchOrder);
            $where = $arrMatchOrder[1];
            $order = $arrMatchOrder[2];
        } else {
            $where = $afterWhere;
            $order = '';
        }

        $arrWhere = $this->forWhere($where);
        list($order, $group) = $this->forOrder($order);
        $arrOrder = $this->orderDeal($order);

        list($group, $having) = $this->forGroup($group);
        $arrGroup  = $this->groupDeal($group);
        $arrHaving = $this->forWhere($having);

//        print_r($arrWhere);
        $sql = sprintf("db.'%s'.aggregate", $table);

        // 合并条件
        $jsonMatch = $this->toMatch($arrWhere);
        $str = '';
        if (!empty($jsonMatch)) {
            $str .= '$match:' . $jsonMatch;
        }
        // 合并排序 TODO HERE
        $jsonSort = $this->toSort($arrOrder);

        var_dump($str);exit;

        return $sql;

//    $arr = ['avg', 'sum', 'max', 'min', 'count'];
    }

    public function forWhere($where)
    {
        $arrWhere = explode('and', $where);
        return array_map(function ($val) {
            return $this->whereDeal($val);
        }, $arrWhere);
    }

    public function whereDeal($str)
    {
        $preg = '/(.*?)(=|>|>=|<|<=|<>|!=|like|is\snot|is|in|not\sin)(.*?)$/';
        preg_match($preg, $str, $arrMatch);
        array_shift($arrMatch);
        return array_map(function ($v) {
            return trim($v);
        }, $arrMatch);
    }

    public function forOrder($order)
    {
        if (empty($order)) {
            return ['', ''];
        }
        if (strpos($order, 'group by') !== false) {
            $preg = '/(.*?)group\sby(.*?)$/';
            preg_match($preg, $order, $arrMatch);
            $order = $arrMatch[1];
            $group = $arrMatch[2];
        } else {
            $group = '';
        }
        return [$order, $group];
    }

    public function orderDeal($str)
    {
        if (empty($str)) {
            return [];
        }
        $arrOrder = explode(',', $str);
        return array_map(function ($v) {
            $v = trim($v);
            return explode(' ', $v);
        }, $arrOrder);
    }

    public function forGroup($group)
    {
        if (empty($group)) {
            return ['', ''];
        }
        if (strpos($group, 'having') !== false) {
            $preg = '/(.*?)having(.*?)$/';
            preg_match($preg, $group, $arrMatch);
            $group  = $arrMatch[1];
            $having = $arrMatch[2];
        } else {
            $having = '';
        }
        return [$group, $having];
    }

    public function groupDeal($str)
    {
        if (empty($str)) {
            return [];
        }
        $arr = explode(',', $str);
        return array_map(function ($v) {
            return trim($v);
        }, $arr);
    }

    /**
     * 条件转为json字符串
     * @param $arrWhere
     * @return false|string
     */
    protected function toMatch($arrWhere)
    {
        // TODO 考虑or
        $arrMatch = [];
        foreach ($arrWhere as $arrVal) {
            $condition = checkInt($arrVal[2]);
            switch ($arrVal[1]) {
                case '=':
                    $arrMatch[$arrVal[0]] = $condition;
                    break;
                case 'is':
                    $arrMatch[$arrVal[0]] = null;
                    break;
                case 'is not':
                    $arrMatch[$arrVal[0]] = ['$ne' => null];
                    break;
                case 'in':
                case 'not in':
                    $arrMatch[$arrVal[0]] = $this->in($arrVal[1], $condition);
                    break;
                case 'like':
                    $condition = str_replace(['"', '\''], '', $condition);
                    $arrMatch[$arrVal[0]] = $this->like($condition);
                    break;
                default:
                    $arrMatch[$arrVal[0]] = [$this->arrCharacter[$arrVal[1]] => $condition];
            }
        }
        return json_encode($arrMatch);
    }

    private function in($character, $condition)
    {
        $condition = substr($condition, 1, strlen($condition) - 2);
        $condition = checkInt(explode(',', $condition));
        return [$this->arrCharacter[$character] => $condition];
    }

    private function like($condition)
    {
        $first = mb_substr($condition, 0, 1, 'utf-8');
        $second = mb_substr($condition, strlen($condition) - 1, 1, 'utf-8');
        if ($first == '%' && $second != '%') {
            $condition = mb_substr($condition, 1);
            $str = '/' . $condition . '^/';
        } elseif ($first != '%' && $second == '%') {
            $condition = mb_substr($condition, 0, strlen($condition) -2);
            $str = '/^' . $condition . '/';
        } elseif ($first == '%' && $second == '%') {
            $condition = mb_substr($condition, 1, strlen($condition) -2);
            $str = '/' . $condition . '/';
        } else {
            $str = '/^' . $condition . '^/';
        }
        return $str;
    }

    public function toSort($arrOrder)
    {
        // TODO 考虑聚合函数如count(*) desc
        $arrOrder = [];

    }
}

